SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
GO

DECLARE @AlteredFlag BIT
SELECT @AlteredFlag	= 0;

IF EXISTS (	SELECT *
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = 'dbo'
				AND TABLE_NAME = 'ProductIndex'
				AND TABLE_TYPE = 'BASE TABLE' )
	-------------------------------------------------------------------------------
	--Table Already Exists - Execute ALTER Statements
	-------------------------------------------------------------------------------
	BEGIN
		
		IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductIndex' AND COLUMN_NAME = 'IndexUpdated' )
		BEGIN
			ALTER TABLE ProductIndex ADD IndexUpdated DATETIME DEFAULT GETDATE() NOT NULL;			
			PRINT 'dbo.ProductIndex.IndexUpdated column added.';			
			SET @AlteredFlag = 1;
		END
		
		IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductIndex' AND COLUMN_NAME = 'AppName' )
		BEGIN
			ALTER TABLE ProductIndex ADD AppName VARCHAR(40) NULL;
			PRINT 'dbo.ProductIndex.AppName column added.';
			SET @AlteredFlag = 1;
		END
		
		IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductIndex' AND COLUMN_NAME = 'HasInLucene' )
		BEGIN
			ALTER TABLE ProductIndex ADD HasInLucene BIT DEFAULT 0 NOT NULL;
			PRINT 'dbo.ProductIndex.HasInLucene column added.';
			SET @AlteredFlag = 1;
		END
		
		IF ( @AlteredFlag = 1 )
			BEGIN
				PRINT 'Table ProductIndex altered with latest changes.';		
				PRINT 'Table ProductIndex successfully upgraded.';
			END
		ELSE
			BEGIN
				PRINT 'Table ProductIndex requires no changes.';
			END
		END
ELSE
	-------------------------------------------------------------------------------
	--Table Does Not Exist - Create the Table
	-------------------------------------------------------------------------------
	BEGIN
		CREATE TABLE [dbo].[ProductIndex](
			[ProductID] [int] NOT NULL,
			[Name] [nvarchar](250) NOT NULL,
			[Description] [nvarchar](3000) NULL,
			[Model] [nvarchar](100) NULL,
			[XMLData] [nvarchar](max) NULL,
			[IndexUpdated] [datetime] NOT NULL,
			[AppName] [varchar](40) NULL,
			[HasInLucene] [bit] NOT NULL,
		 CONSTRAINT [PK_ProductIndex] PRIMARY KEY CLUSTERED 
		(
			[ProductID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]

		ALTER TABLE [dbo].[ProductIndex] ADD  DEFAULT (getdate()) FOR [IndexUpdated]
		
		ALTER TABLE [dbo].[ProductIndex] ADD  DEFAULT ((0)) FOR [HasInLucene]
		
	-------------------------------------------------------------------------------
	--	END OF CREATE STATEMENTS.
	--	METADATA AND SUCCESS/ERROR MESSAGES BELOW.
	-------------------------------------------------------------------------------
	-- Make sure object was created successfully.
	IF EXISTS (	SELECT *
				FROM INFORMATION_SCHEMA.TABLES
				WHERE TABLE_SCHEMA = 'dbo'
					AND TABLE_NAME = 'ProductIndex'
					AND TABLE_TYPE = 'BASE TABLE' )


		BEGIN
			PRINT 'Table ProductIndex created successfully.';	
		END
	ELSE
		BEGIN
			PRINT 'ERROR: Failed to create Table ProductIndex.';
		END
	END
GO



